/**
 * @开发版权 
 * @项目名称 轻量数据中台(LDMP)
 * @版本信息 v1.0
 * @开发人员 zhous
 * @开发日期 2025-01-17
 * @修订日期
 * @描述  RoleController
 */
package com.ng.biz.controller;

import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.ArrayList;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import com.alibaba.fastjson.JSONObject;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.ng.biz.service.IDatasource;
import com.ng.common.annotation.TokenRequired;
import com.ng.common.annotation.Tx;
import com.ng.common.controller.Controller;
import com.ng.common.model.dto.ParsedSql;
import com.ng.common.model.response.R;
import com.ng.common.util.ParsedSqlUtil;
import com.ng.common.util.UuidUtil;
import com.ng.common.util.WebUtil;
import com.ng.biz.model.domain.Datasource;
import com.ng.biz.model.domain.DatasourceGroup;
import com.ng.biz.model.request.ExecSqlRequest;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.Parameter;
import io.swagger.v3.oas.annotations.Parameters;
import io.swagger.v3.oas.annotations.tags.Tag;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import lombok.RequiredArgsConstructor;
import com.jfinal.plugin.activerecord.Record;

@Tag(name = "数据源管理")
@RequiredArgsConstructor
@RestController
@RequestMapping("/biz/datasource")
public class DatasourceController extends Controller{

	@Autowired
	private IDatasource datasourceImpl;
	
	
	@Operation(summary = "获取数据源分组列表")
	@Parameters({ @Parameter(name = "name", description = "组名", required = false) })
	@TokenRequired
	@GetMapping(value = { "/group/list" }, produces = "application/json; charset=utf-8")
	public R<List<DatasourceGroup>> getDbapiGroups(HttpServletRequest request,
			@RequestParam(name = "name", required = false, defaultValue = "") String name) throws Exception {
		String sqlExceptSelect = " from " + DatasourceGroup.TABLE_NAME + "  a WHERE 1=1 ";
		if (!StrKit.isBlank(name)) {
			sqlExceptSelect += " and a.name like '%" + name + "%' ";
		}
		sqlExceptSelect += "  ORDER BY a.sort asc ";
		return R.ok(DatasourceGroup.dao.find("select a.* " + sqlExceptSelect));
	}

	@Operation(summary = "保存数据源分组")
	@Parameters({ @Parameter(name = "group", description = "Dbapi 分组对象", required = true) })
	@TokenRequired
	@Tx
	@RequestMapping(value = "/group/save", method = RequestMethod.POST)
	public R<DatasourceGroup> saveDbapiGroup(HttpServletRequest request, @RequestBody DatasourceGroup group) {
		try {
			if (StrKit.notBlank(group.getId())) {
				group.setUpdateUserId(getUserId(request));
				group.setUpdateTime(new Date(System.currentTimeMillis()));
				if (group.update()) {
					return R.ok(group);
				} else {
					return R.failed("更新失败！");
				}
			} else {
				group.setId(UuidUtil.getUUID());
				group.setCreateUserId(getUserId(request));
				group.setCreateTime(new Date(System.currentTimeMillis()));
				if (group.save()) {
					return R.ok(group);
				} else {
					return R.failed("保存失败.");
				}
			}
		} catch (Exception e) {
			return R.failed(e.getMessage());
		}
	}

	@Operation(summary = "删除数据源分组")
	@Parameters({ @Parameter(name = "ids", description = "数据源分组id集合", required = true) })
	@DeleteMapping("/group/{ids}")
	@TokenRequired
	public R<String> deleteDatasourceGroupByIds(@PathVariable("ids") String ids) {
		return datasourceImpl.deleteDatasourceGroupByIds(ids);
	}
	
	@Operation(summary = "分页获取数据源列表")
	@Parameters({ @Parameter(name = "keywords", description = "关键字", required = false),
		@Parameter(name = "cur", description = "当前页码", required = true),
		@Parameter(name = "limit", description = "每页数据量", required = true)})
	@TokenRequired
	@GetMapping(value = { "/pages" }, produces = "application/json; charset=utf-8")
	public R<Page<Datasource>> pages(HttpServletRequest request,
			@RequestParam(name = "keywords", required = false, defaultValue = "") String keywords,
			@RequestParam(name = "cur", required = false, defaultValue = "1") int cur,
			@RequestParam(name = "limit", required = false, defaultValue = "10") int limit) throws Exception {
		String sqlExceptSelect = " from " + Datasource.TABLE_NAME + "  a WHERE 1=1 ";
		if (!StrKit.isBlank(keywords)) {
			sqlExceptSelect += " and (a.name like '%" + keywords + "%' or a.code like '%" + keywords + "%') ";
		}
		sqlExceptSelect += "  ORDER BY a.update_time desc ";
		
		Page<Datasource> page=Datasource.dao.paginate(cur, limit, "select a.* ", sqlExceptSelect);
		for(Datasource datasource:page.getList()) {
			datasource.setIsRunning(datasourceImpl.isRunning(datasource.getId()));
		}
		return R.ok(page);
	}

	
	@Operation(summary = "保存数据源")
	@Parameters({ 
		@Parameter(name = "datasource", description = "数据源信息", required = true)})
	@TokenRequired
	@Tx
	@RequestMapping(value = "/save", method = RequestMethod.POST)
	public R<Datasource> save(HttpServletRequest request, @RequestBody Datasource datasource) {
		try {
			if (StrKit.notBlank(datasource.getId())) {
				Datasource checkDatasource=Datasource.dao.findFirst("select * from "+Datasource.TABLE_NAME+" where id!=? and code=? ",datasource.getId(),datasource.getCode());
				if(checkDatasource!=null) {
					return R.failed("数据源标识已存在！");
				}
				datasource.setUpdateUserId(getUserId(request));
				if (datasource.update()) {
					return R.ok(datasource);
				} else {
					return R.failed("更新失败！");
				}
			} else {
				Datasource checkDatasource=Datasource.dao.findFirst("select * from "+Datasource.TABLE_NAME+" where code=? ",datasource.getCode());
				if(checkDatasource!=null) {
					return R.failed("数据源标识已存在！");
				}
				datasource.setId(UuidUtil.getUUID());
				datasource.setCreateUserId(getUserId(request));
				if (datasource.save()) {
					return R.ok(datasource);
				} else {
					return R.failed("保存失败.");
				}
			}
		} catch (Exception e) {
			return R.failed(e.getMessage());
		}
	}

	@Operation(summary = "删除数据源")
	@Parameters({ 
		@Parameter(name = "ids", description = "数据源id集合(逗号分割)", required = true)})
	@DeleteMapping("/{ids}")
	@TokenRequired
	public R<String> delete(@PathVariable("ids") String ids) {
		String[] arr=ids.split(",");
		if(ids==null || ids.length()<=0) {
			return R.failed("请选择需要删除的数据源.");
		}
		for(String id:arr) {
			Datasource.dao.deleteById(id);
		}
		return R.ok();
	}

	@Operation(summary = "数据源下拉列表")
	@Parameters({ @Parameter(name = "keywords", description = "关键字", required = false),@Parameter(name = "src", description = "源", required = false)})
	@TokenRequired
	@GetMapping("/options")
	public R<List<Datasource>> listRoleOptions(HttpServletRequest request,
			@RequestParam(name = "keywords", required = false, defaultValue = "") String keywords,
			@RequestParam(name = "src", required = false, defaultValue = "OLTP") String src) {
		String sqlExceptSelect = " from " + Datasource.TABLE_NAME + "  a WHERE 1=1 ";
		if (!StrKit.isBlank(keywords)) {
			sqlExceptSelect += " and (a.name like '%" + keywords + "%' or a.code like '%" + keywords + "%') ";
		}
		if(!StrKit.isBlank(src)) {
			sqlExceptSelect += " and a.src ='"+src+"' ";
		}
		List<Datasource> options = Datasource.dao.find("select id,name "+sqlExceptSelect+" order by update_time desc");
		return R.ok(options);
	}
	
	@Operation(summary = "测试数据源")
	@Parameters({ @Parameter(name = "id", description = "数据源Id", required = true) })
	@PostMapping("/test/{id}")
	@TokenRequired
	public R<String> test(@PathVariable("id") String id) {
		return datasourceImpl.test(id);
	}
	
	@Operation(summary = "启动数据源")
	@Parameters({ @Parameter(name = "id", description = "数据源Id", required = true) })
	@PostMapping("/start/{id}")
	@TokenRequired
	public R<String> start(@PathVariable("id") String id) {
		return datasourceImpl.start(id);
	}
	
	@Operation(summary = "停止数据源")
	@Parameters({ @Parameter(name = "id", description = "数据源Id", required = true) })
	@PostMapping("/stop/{id}")
	@TokenRequired
	public R<String> stop(@PathVariable("id") String id) {
		return datasourceImpl.stop(id);
	}
	
	
	
	@Operation(summary = "执行sql")
	@Parameters({ 
		@Parameter(name = "execSqlRequest", description = "执行信息", required = true)})
	@TokenRequired
	@RequestMapping(value = "/exec/sql", method = RequestMethod.POST)
	public void execSql(HttpServletRequest request, HttpServletResponse response, @RequestBody ExecSqlRequest execSqlRequest) {
		try {
			ParsedSql parsedSql = validateAndParseSql(execSqlRequest);
			if(parsedSql == null) {
				if(StrKit.isBlank(execSqlRequest.getContent())) {
					WebUtil.renderJson(response, R.failed("SQL验证失败"));
					return;
				}
				WebUtil.renderJson(response, R.ok(Db.use(execSqlRequest.getDatasourceId()).findFirst(execSqlRequest.getContent())));
				return;
			}
			WebUtil.renderJson(response, R.ok(Db.use(execSqlRequest.getDatasourceId()).findFirst(parsedSql.getSql(),parsedSql.getParams().toArray())));
			
		} catch (Exception e) {
			WebUtil.renderJson(response, R.failed(e.getMessage()));
		}
	}

	@Operation(summary = "获取SQL查询结果的字段信息")
	@Parameters({ 
		@Parameter(name = "execSqlRequest", description = "执行信息", required = true)})
	@TokenRequired
	@RequestMapping(value = "/sql/fields", method = RequestMethod.POST) 
	public R<List<JSONObject>> getSqlFields(@RequestBody ExecSqlRequest execSqlRequest) {
		try {
			ParsedSql parsedSql = validateAndParseSql(execSqlRequest);
			if(parsedSql == null) {
				if(StrKit.isBlank(execSqlRequest.getContent())) {
					return R.failed("SQL验证失败");
				}
				return R.ok(getFieldsWithComments(execSqlRequest.getDatasourceId(), execSqlRequest.getContent()));
			}
			List<JSONObject> fields = getFieldsWithComments(execSqlRequest.getDatasourceId(), parsedSql.getSql());
			return R.ok(fields);
		} catch (Exception e) {
			return R.failed(e.getMessage());
		}
	}
	
	// 获取字段及其备注信息
	private List<JSONObject> getFieldsWithComments(String datasourceId, String sql) {
		List<JSONObject> fields = new ArrayList<>();
		
		// 解析SQL获取表名
		String tableName = extractTableName(sql);
		if (StrKit.isBlank(tableName)) {
			return fields;
		}
		
		// 执行查询以获取字段信息
		Page<Record> page = Db.use(datasourceId).paginate(1, 1, sql, "");
		List<Record> records = page.getList();
		if(records == null || records.isEmpty()) {
			return fields;
		}
		
		Record record = records.get(0);
		Map<String, Object> columns = record.getColumns();
		
		for(Map.Entry<String, Object> entry : columns.entrySet()) {
			JSONObject field = new JSONObject();
			
			// 获取字段备注信息
			String comment = getFieldComment(datasourceId, tableName, entry.getKey());
			
			// 输入字段信息
			field.put("inputFieldName", StrKit.notBlank(comment) ? comment : entry.getKey());
			field.put("inputFieldIdentifier", entry.getKey());
			field.put("inputFieldType", getFieldType(entry.getValue()));
			field.put("inputFieldFormat", "");
			field.put("inputFieldValueMapping", "none");
			field.put("inputFieldValueMappingObject", "none");
			
			// 字段名和标识符是否相同的标志
			field.put("sameFieldName", "1");
			field.put("sameFieldIdentifier", "1");
			
			// 输出字段信息 (默认与输入相同)
			field.put("outputFieldName", field.getString("inputFieldName"));
			field.put("outputFieldIdentifier", field.getString("inputFieldIdentifier"));
			field.put("outputFieldType", field.getString("inputFieldType"));
			field.put("outputFieldFormat", "");
			field.put("outputFieldDefaultValue", "");
			field.put("outputValueFromInput", "input");
			
			fields.add(field);
		}
		
		return fields;
	}
	
	// 获取字段备注信息的方法
	private String getFieldComment(String datasourceId, String tableName, String fieldName) {
		String comment = ""; // 默认空字符串
		
		try {
			// 获取数据源信息
			Datasource datasource = Datasource.dao.findById(datasourceId);
			if (datasource == null) {
				return comment;
			}
			
			// 根据数据源类型获取字段注释
			String type = datasource.getType();
			Record commentRecord = null;
			
			switch (type.toLowerCase()) {
				case "mysql":
					commentRecord = Db.use(datasourceId).findFirst(
						"SELECT COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS " +
						"WHERE TABLE_NAME = ? AND COLUMN_NAME = ? AND TABLE_SCHEMA = DATABASE()", 
						tableName, fieldName);
					break;
					
				case "oracle":
					commentRecord = Db.use(datasourceId).findFirst(
						"SELECT COMMENTS AS COLUMN_COMMENT FROM USER_COL_COMMENTS " +
						"WHERE TABLE_NAME = ? AND COLUMN_NAME = ?",
						tableName.toUpperCase(), fieldName.toUpperCase());
					break;
					
				case "postgresql":
					commentRecord = Db.use(datasourceId).findFirst(
						"SELECT col_description(a.attrelid, a.attnum) AS COLUMN_COMMENT " +
						"FROM pg_catalog.pg_attribute a " +
						"WHERE a.attrelid = ?::regclass AND a.attname = ?",
						tableName, fieldName);
					break;
					
				case "sqlserver":
					commentRecord = Db.use(datasourceId).findFirst(
						"SELECT CAST(p.value AS NVARCHAR(MAX)) AS COLUMN_COMMENT " +
						"FROM sys.tables t " +
						"JOIN sys.columns c ON t.object_id = c.object_id " +
						"LEFT JOIN sys.extended_properties p ON p.major_id = t.object_id " +
						"AND p.minor_id = c.column_id AND p.name = 'MS_Description' " +
						"WHERE t.name = ? AND c.name = ?",
						tableName, fieldName);
					break;
					
				default:
					// 不支持的数据库类型
					return comment;
			}
			
			if (commentRecord != null) {
				comment = commentRecord.getStr("COLUMN_COMMENT");
			}
		}
		catch (Exception e) {
			// 获取注释失败时返回空字符串
			return "";
		}
		
		return comment;
	}
	
	/**
	 * 从SQL中提取表名
	 */
	private String extractTableName(String sql) {
		try {
			// 转换为小写便于处理
			sql = sql.toLowerCase();
			// 移除多余空格
			sql = sql.replaceAll("\\s+", " ").trim();
			
			// 查找 FROM 子句
			int fromIndex = sql.indexOf(" from ");
			if (fromIndex == -1) {
				return null;
			}
			
			// 获取 FROM 后面的内容
			String fromClause = sql.substring(fromIndex + 6);
			
			// 查找第一个空格或其他分隔符
			int endIndex = fromClause.indexOf(" ");
			if (endIndex == -1) {
				endIndex = fromClause.length();
			}
			
			// 提取表名
			String tableName = fromClause.substring(0, endIndex).trim();
			
			// 移除可能的别名
			if (tableName.contains(" as ")) {
				tableName = tableName.substring(0, tableName.indexOf(" as ")).trim();
			}
			
			// 移除数据库名前缀（如果有）
			if (tableName.contains(".")) {
				tableName = tableName.substring(tableName.lastIndexOf(".") + 1);
			}
			
			return tableName;
		} catch (Exception e) {
			return null;
		}
	}
	
	/**
	 * 验证并解析SQL
	 */
	private ParsedSql validateAndParseSql(ExecSqlRequest execSqlRequest) {
		if (StrKit.isBlank(execSqlRequest.getDatasourceId())) {
			return null;
		}
		
		Datasource datasource = Datasource.dao.findById(execSqlRequest.getDatasourceId());
		if(datasource == null) {
			return null;
		}
		
		if(StrKit.equals("0", datasourceImpl.isRunning(execSqlRequest.getDatasourceId()))) {
			return null;
		}
		
		String sql = execSqlRequest.getContent();
		if(execSqlRequest.getFields() != null && execSqlRequest.getFields().size() > 0) {
			return ParsedSqlUtil.parseSql(sql, execSqlRequest.getFields(), execSqlRequest.getValues());
		}
		return null;
	}
	
	/**
	 * 获取字段类型
	 */
	private String getFieldType(Object value) {
		if(value == null) return "string";
		if(value instanceof Integer) return "int";
		if(value instanceof Long) return "long";
		if(value instanceof Float) return "float";
		if(value instanceof Double) return "double";
		if(value instanceof Boolean) return "boolean";
		if(value instanceof Date) return "date";
		return "string";
	}

}
